select * from emps

Select Name,Dept,EncryptByKey(Key_Guid(N'AdvSym'),Name) as EncryptedName,
Convert(Varchar(20), DecryptByKey(EncryptedName)) as DecryptedName from emps

--you need to open master key
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@Ss28'
GO

--put the service level encryption back
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

--restore your Database master key from file
RESTORE MASTER KEY FROM FILE = 'C:\Encry1.DMK'
DECRYPTION BY PASSWORD = 'p@Ss28' 
ENCRYPTION BY PASSWORD='p@Ss28' 
GO

--open symmetric key,
--no need to create it as it was there in backup
Open Symmetric key AdvSym
Decryption by certificate AdvCert
GO


BACKUP CERTIFICATE AdvCert
TO FILE = 'C:\AdvCert.CER'
WITH PRIVATE KEY
(
      FILE='C:\AdvCert.PVK',
      ENCRYPTION BY PASSWORD='p@Ss28'
)
go

Drop Symmetric Key AdvSym

Drop Certificate AdvCert


CREATE CERTIFICATE AdvCert
FROM FILE = 'C:\AdvCert.CER'

ALTER CERTIFICATE AdvCert 
    WITH PRIVATE KEY (FILE = 'C:\AdvCert.PVK',
    DECRYPTION BY PASSWORD = 'p@Ss28');
GO

--Create One Certificate
Create Certificate AdvCert
With Subject= 'Certificate to encrypt emps table',
Start_date = '2009-09-29',
Expiry_date ='2012-02-07'
GO

CREATE CERTIFICATE AdvCert
FROM FILE='C:\AdvCert.CER',
Decryption By Password = 'p@Ss28'

Create Symmetric Key AdvSym
With Algorithm = AES_256
Encryption by Certificate AdvCert
GO

BACKUP CERTIFICATE AdvCert
TO FILE = 'C:\AdvCert.CER'
WITH PRIVATE KEY
(
      FILE='C:\AdvCert.PVK',
      ENCRYPTION BY PASSWORD='p@Ss28'
)
go

CREATE CERTIFICATE AdvCert
FROM FILE = 'C:\AdvCert.CER'

ALTER CERTIFICATE AdvCert 
    WITH PRIVATE KEY (FILE = 'C:\AdvCert.PVK',
    DECRYPTION BY PASSWORD = 'p@Ss28');
GO

Open Symmetric Key AdvSym
Decryption By Certificate AdvCert

Select Name,Dept,EncryptByKey(Key_Guid(N'AdvSym'),Name) as EncryptedName,
Convert(Varchar(20), DecryptByCert(EncryptedName)) as DecryptedName from emps

Select * from emps
Close Symmetric key AdvSym